<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of content_model
 *
 * @author phamtruong
 */
class content_model extends CI_Model {

    //put your code here
    public function __construct() {
        parent::__construct();
    }

    //select imformation at table article with id = avalible
    public function article($id) {
//        $this->db->select("*");
        $sql = "SELECT a.id, a.title, v.content, v.version
                FROM article a, article_version v
                WHERE   a.id = $id
                    AND a.id=v.article_id
                    AND v.version=(select max(version) from article_version where article_id=$id)";
        $query = $this->db->query($sql);
        print ($sql);
        return $query->result_array();
    }

    public function article_version($id) {
        $this->db->select("*");
        $this->db->where("article_id", "$id");
        $quere = $this->db->get("article_version");
        return $quere->result_array();
        //select max(version) from  where
    }

    //get version lastest at table article_version
    public function new_version($id) {
        $query = $this->db->query("select max(version)from article_version where article_id = $id");
        return $query->result_array();
    }

    //get  version oldest at table article_version
    public function oldest_version($id) {
        $query = $this->db->query("select min(version) from article_version where article_id = $id");
        return $query->result_array();
    }

    //get content with lastest version
    public function get_content($new_version) {
        $query = $this->db->query("SELECT  `content` FROM  `article_version` WHERE  `version` = $new_version");
        return $query->result_array();
    }

    //get contributor_id at table where version
//    public function get_contributor($id) {
//        $query = $this->db->query("SELECT  `contributor_id` FROM  `article_version` WHERE  `version` = $id");
//        $a = $query->result_array();
//        return $a[0]['contributor_id'];
//    }

    //get imformation member create article from contributor_id older version
    public function member_create($id_article) {
        $query = $this->db->query("
            select m.fullname
            from member m, article_version v
            where m.id=v.contributor_id 
            AND v.version=(select min(version) from article_version where v.article_id=$id_article)
                 ");
        return $query->result_array();
    }

    // get date created article by oder version
    public function get_date($id_article) {
        $query = $this->db->query("
            select v.modified_date
            from article a, article_version v
            where a.id=v.article_id 
            AND v.version=(select min(version) from article_version where v.article_id= $id_article)
            ");
        return $query->result_array();
    }
    //get contributor

    public function get_contributor($id_article){
        $query= $this->db->query("
            select m.fullname
            from member m, article_version v
            where m.id=v.contributor_id 
            AND v.version !=(select min(version) from article_version where v.article_id=$id_article)
            ");
        return $query->result_array();
    }
    public function get_history($id){
        $query=  $this->db->query("SELECT v.version, a.id, v.modified_date, m.fullname, a.title 
FROM 
article_version v, article a, member m
WHERE
v.article_id= '$id'
and v.contributor_id = m.id
and a.id= '$id'");
        return $query->result_array();
    }
    public function get_story($id,$version){
        $query=  $this->db->query("select a.title, b.fullname, v.modified_date, v.content
from article a,member b, article_version v
where a.id='$id' 
and v.version='$version'
and v.contributor_id= b.id
AND a.id=v.article_id");
        return $query->result_array();
    }
    
    
    public function insert_article($data){
        $title =  $this->db->escape_str($data["title"]);
        $permission = $this->db->escape_str($data["permission"]);
        $content =  $this->db->escape_str($data["content"]);
        //insert into article table
        $query1 = $this->db->query("INSERT INTO `article`(`title`, `permission`) VALUES ( '$title', ".$permission.")");
        $insertedArticleID= $this->db->insert_id();
        //insert into article_version table
        $query2 = $this->db->query("INSERT INTO `article_version`( `content`, `version`, `modified_date`, `article_id`, `contributor_id`) 
            VALUES ('" . $content . "',1,now(), ". $insertedArticleID .", " . $data["member_id"] . ")");
        return $insertedArticleID;
    }
}

/* End of file content_model.php */
/* Location: ./application/models/content_model.php */